package com.ncloud.chnl.mapper;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.ncloud.chnl.entity.CustUser;  
@Mapper
public interface CustUserMapper{  
  
    @Select(value = {"select a.user_id,b.cust_id,b.cust_name,d.area_id,(select area_name from bs_cust_area where area_id = d.area_id) as area_name,c.account,"
    		+ " c.name as user_name,c.email,c.mobile,c.user_type,f_get_label('USER_TYPE',c.user_type) as user_type_name,"
    		+ " c.state,f_get_label('USER_STATE',c.state) as state_name,c.telephone,c.remark from bs_cust_user_rel a"
    		+ " LEFT JOIN bs_cust_area_user_rel d on d.cust_id = a.cust_id and d.state='1' and a.user_id = d.user_id,bs_cust b ,sys_user c"
    		+ " where a.state ='1' and c.state <>'0' and a.cust_id = #{custId} and a.cust_id = b.cust_id and b.cust_state = '1' and a.user_id = c.id" })
	@Results({ @Result(column = "cust_name", property = "custName"),@Result(column = "area_name", property = "areaName"),
		@Result(javaType=String.class,column = "user_id", property = "userId"),@Result(column = "account", property = "account"),
		@Result(javaType=String.class,column = "cust_id", property = "custId"),
		@Result(javaType=String.class, column = "area_id", property = "areaId"),
		@Result(column = "user_name", property = "userName"), @Result(column = "email", property = "email"),
		@Result(column = "mobile", property = "mobile"),@Result(column = "remark", property = "remark"),
		@Result(column = "user_type", property = "userType"),@Result(column = "user_type_name", property = "userTypeName"),
		@Result(column = "state", property = "state"), @Result(column = "state_name", property = "stateName")})
    public List<Map<String,Object>> getCustUser(@Param("custId")Long custId);
    
    @Select(value = {"select a.user_id,b.cust_id,b.cust_name,c.account, c.name as user_name,c.email,c.mobile,c.user_type,"
    		+ " f_get_label('USER_TYPE',c.user_type) as user_type_name, c.state,f_get_label('USER_STATE',c.state) as state_name,"
    		+ " c.telephone,c.remark from bs_cust_user_rel a,bs_cust b ,sys_user c where a.state ='1' and c.state <>'0' "
    		+ " and a.cust_id = #{custId} and a.cust_id = b.cust_id and b.cust_state = '1' and a.user_id = c.id" })
	@Results({ @Result(column = "cust_name", property = "custName"),@Result(column = "area_name", property = "areaName"),
		@Result(javaType=String.class,column = "user_id", property = "userId"),@Result(column = "account", property = "account"),
		@Result(javaType=String.class,column = "cust_id", property = "custId"),
		@Result(javaType=String.class, column = "area_id", property = "areaId"),
		@Result(column = "user_name", property = "userName"), @Result(column = "email", property = "email"),
		@Result(column = "mobile", property = "mobile"),@Result(column = "remark", property = "remark"),
		@Result(column = "user_type", property = "userType"),@Result(column = "user_type_name", property = "userTypeName"),
		@Result(column = "state", property = "state"), @Result(column = "state_name", property = "stateName")})
    public List<Map<String,Object>> getCustUsers(@Param("custId")Long custId);
    
    @Select(value = {"select * from bs_cust_user_rel where user_id = #{userId} and state ='1'" })
	@Results({ @Result(column = "rel_inst_id", property = "relInstId"),@Result(column = "cust_id", property = "custId"),
			@Result(column = "user_id", property = "userId"),
			@Result(column = "state", property = "state"), @Result(column = "create_time", property = "createTime"),
			@Result(column = "create_user", property = "createUser"),
			@Result(column = "update_time", property = "updateTime"),
			@Result(column = "update_user", property = "updateUser"), @Result(column = "remark", property = "remark")})
    public CustUser getCustByUserId(@Param("userId")Long userId);
  
    @Select(value = {"select a.user_id,b.cust_id,b.cust_name,d.area_id,(select area_name from bs_cust_area where area_id = d.area_id) as area_name,c.account,"
    		+ " c.name as user_name,c.email,c.mobile,c.telephone,c.remark,c.user_type,f_get_label('USER_TYPE',c.user_type) as user_type_name,"
    		+ " c.state,f_get_label('USER_STATE',c.state) as state_name from bs_cust_user_rel a"
    		+ " LEFT JOIN bs_cust_area_user_rel d on d.cust_id = a.cust_id and d.state='1' and a.user_id = d.user_id,bs_cust b ,sys_user c"
    		+ " where a.state ='1'and a.cust_id = b.cust_id and b.cust_state = '1' and a.user_id = c.id and c.state<>'0' order by b.cust_name,c.account" })  
	@Results({ @Result(column = "cust_name", property = "custName"),@Result(column = "area_name", property = "areaName"),
			@Result(javaType=String.class, column = "user_id", property = "userId"),@Result(column = "account", property = "account"),
			@Result(javaType=String.class, column = "cust_id", property = "custId"),
			@Result(javaType=String.class, column = "area_id", property = "areaId"),
			@Result(column = "user_name", property = "userName"), @Result(column = "email", property = "email"),
			@Result(column = "mobile", property = "mobile"),
			@Result(column = "user_type", property = "userType"),@Result(column = "user_type_name", property = "userTypeName"),
			@Result(column = "state", property = "state"), @Result(column = "state_name", property = "stateName")}) 
    public List<Map<String,Object>> getAllCustUsers();


    @Insert(value = ("insert into bs_cust_user_rel(rel_inst_id,cust_id,user_id,is_manager,state,create_time,create_user,update_time,update_user,remark)"
    		+ " values (#{relInstId},#{custId},#{userId},#{isManager},#{state},#{createTime},#{createUser},#{updateTime},#{updateUser},#{remark})"))
    public int insertCustUser(CustUser CustUser);
    
    @Update(value = ("update bs_cust_user_rel set state = #{state},update_time = now(),"
    		+ " update_user = #{updateUser},remark = #{remark} where rel_inst_id = #{relInstId}"))
    public int updateCustUser(CustUser CustUser);


	@Select(value = {"select * from bs_cust_user_rel where cust_id = #{custId} and state ='1'"})
	@Results({ @Result(column = "rel_inst_id", property = "relInstId"),@Result(column = "cust_id", property = "custId"),
			@Result(column = "user_id", property = "userId"),
			@Result(column = "state", property = "state"), @Result(column = "create_time", property = "createTime"),
			@Result(column = "create_user", property = "createUser"),
			@Result(column = "update_time", property = "updateTime"),
			@Result(column = "update_user", property = "updateUser"), @Result(column = "remark", property = "remark")})
	public List<CustUser> getUserIdByCustId(@Param("custId")Long custId);
    
} 